use master;
go
if exists (select * from SYSDATABASES where name ='Tunga')
BEGIN
	drop database Tunga;
END
	create database Tunga;
go
use Tunga;
go
--drop
/*-------- TABLES --------*/
create table [Admin](
	[id] int identity(1,1) primary key,
	[username] nvarchar(100),		
	[password] nvarchar(100)
);
go
create table [Customer] (
	[id] int identity(1,1) primary key,
	[username] nvarchar(100),
	[password] nvarchar(100),
	[fullname] nvarchar(100),
	[dob] datetime,
	[gender] bit,
	[address] nvarchar(100),
	[phone] nvarchar(100),
	[email] nvarchar(100),
	[count] int
);
go
create table [City] (
	[id] int identity(1,1) primary key,
	[name] nvarchar(100)
);
go
create table [Restaurant] (
	[id] int identity(1,1) primary key,
	[cityid] int references [City]([id]) ON UPDATE CASCADE  ON DELETE CASCADE,
	[name] nvarchar(100),
	[username] nvarchar(100),
	[password] nvarchar(100),
	[managername] nvarchar(100),
	[phone] nvarchar(100),
	[address] nvarchar(100),
	[picture] nvarchar(500),
	[descr] nvarchar(500),
	[status] bit default(0)
);
go
create table [TableType](
	[id] int identity(1,1) primary key,
	[typename] nvarchar(100),
	[capacity] int,	
	[position] bit,
	[price] float,
	[picture] nvarchar(500),
	[descr] nvarchar(500),
	[status] bit default(0)
);
go
create table [Table] (
	[id] int identity(1,1) primary key,	
	[tabletypeid] int references [TableType]([id]) ON UPDATE CASCADE  ON DELETE CASCADE	
);
go
create table [FoodType] (
	[id] int identity(1,1) primary key,
	[typename] bit
);
go
create table [Food] (
	[id] int identity(1,1) primary key,	
	[foodtypeid] int references [FoodType]([id]) ON UPDATE CASCADE  ON DELETE CASCADE,
	[name] nvarchar(100),
	[price] float,
	[persons] int,
	[picture] nvarchar(500),
	[descr] nvarchar(500),
	[status] bit default(0)
);
go
create table [BookingTable] (
	[id] int identity(1,1) primary key,
	[customerid] int references [Customer]([id]) ON UPDATE CASCADE  ON DELETE CASCADE,
	[restaurantid] int references [Restaurant]([id]) ON UPDATE CASCADE  ON DELETE CASCADE,
	[bookingdate] datetime,
	[bookingtime] datetime,
	[totalmoney] float,
	[email] nvarchar(100),
	[phone] nvarchar(100),
	[descr] nvarchar(500),
	[status] bit default(0)
);
go
create table [BookingTableDetail] (
	[id] int identity(1,1) primary key,
	[bookingtableid] int references [BookingTable]([id]) ON UPDATE CASCADE  ON DELETE CASCADE,
	[tableid] int references [Table]([id]) ON UPDATE CASCADE  ON DELETE CASCADE,
	[money] float,
	[quantity] int
);
go
create table [OrderFood] (
	[id] int identity(1,1) primary key,
	[customerid] int references [Customer]([id]) ON UPDATE CASCADE  ON DELETE CASCADE,
	[restaurantid] int references [Restaurant]([id]) ON UPDATE CASCADE  ON DELETE CASCADE,	
	[orderdate] datetime,
	[ordertime] datetime,
	[totalmoney] float,
	[address] nvarchar(100),
	[email] nvarchar(100),
	[phone] nvarchar(100),
	[descr] nvarchar(500),
	[status] bit default(0)
);
go
create table [OrderFoodDetail] (
	[id] int identity(1,1) primary key,
	[orderfoodid] int references [OrderFood]([id]) ON UPDATE CASCADE  ON DELETE CASCADE,
	[foodid] int references [Food]([id]) ON UPDATE CASCADE  ON DELETE CASCADE,
	[money] float,
	[quantity] int
);
create table [Events] (
	[id] int identity(1,1) primary key,
	[title] nvarchar(100),
	[datepost] datetime,
	[descr] nvarchar(500)	
);
--INSERT--
go
insert into [Admin] values ('admin','admin');
insert into [Admin] values ('sa','123');
insert into [Admin] values ('hieunt','123');
go
insert into [Customer] values ('user1','123','Tran A','01/30/1989','true','101 Quang Trung','0123456777','trana@gmail.com','0');

insert into [Customer] values ('user2','123','Tran B','02/28/1989','false','102 Quang Trung','0123456788','tranb@gmail.com','2');

insert into [Customer] values ('user3','123','Tran C','03/30/1989','true','103 Quang Trung','0123456799','tranb@gmail.com','0');
go
insert into [City] values('Hanoi');
insert into [City] values('Haiphong');
insert into [City] values('Hue');
insert into [City] values('Nhatrang');
insert into [City] values('Hochiminh');
go
insert into [Restaurant] values ('1','Tunga Ha Noi','tunga1','tunga1','Nguyen A','043446685','99 Tay Son','','Brunch: Sunday: 11:00am - 2:00pm, Lunch: Saturday: 11:00am - 4:30pm, Dinner Nightly: 4:30pm - 10:00pm','1');
insert into [Restaurant] values ('2','Tunga Hai Phong','tunga2','tunga2','Nguyen B','044446686','99 Hung Vuong','','Brunch: Sunday: 11:00am - 2:00pm, Lunch: Saturday: 11:00am - 4:30pm, Dinner Nightly: 4:30pm - 10:00pm','1');
insert into [Restaurant] values ('3','Tunga Hue','tunga3','tunga3','Nguyen C','045446687','99 Tay Son','','Brunch: Sunday: 11:00am - 2:00pm, Lunch: Saturday: 11:00am - 4:30pm, Dinner Nightly: 4:30pm - 10:00pm','1');
insert into [Restaurant] values ('4','Tunga Nha Trang','tunga4','tunga4','Nguyen D','046446688','99 Tay Son','','Brunch: Sunday: 11:00am - 2:00pm, Lunch: Saturday: 11:00am - 4:30pm, Dinner Nightly: 4:30pm - 10:00pm','1');
insert into [Restaurant] values ('5','Tunga Ho Chi Minh','tunga5','tunga5','Nguyen E','084-047446689','99 Tay Son','','Brunch: Sunday: 11:00am - 2:00pm, Lunch: Saturday: 11:00am - 4:30pm, Dinner Nightly: 4:30pm - 10:00pm','true');
go
insert into [TableType] values ('Level 1','2','false','25.0','','','true');
insert into [TableType] values ('Level 1','2','true','30.0','','','true');
insert into [TableType] values ('Level 1','4','false','35.0','','','true');
insert into [TableType] values ('Level 1','4','true','40.0','','','true');
insert into [TableType] values ('Level 1','6','false','45.0','','','true');
insert into [TableType] values ('Level 1','6','true','50.0','','','true');
go
insert into [TableType] values ('Level 2','2','false','40.0','','','true');
insert into [TableType] values ('Level 2','2','true','45.0','','','true');
insert into [TableType] values ('Level 2','4','false','50.0','','','true');
insert into [TableType] values ('Level 2','4','true','55.0','','','true');
insert into [TableType] values ('Level 2','6','false','60.0','','','true');
insert into [TableType] values ('Level 2','6','true','65.0','','','true');
go
insert into [Table] values ('1');
insert into [Table] values ('2');
insert into [Table] values ('3');
insert into [Table] values ('4');
insert into [Table] values ('5');
insert into [Table] values ('6');
insert into [Table] values ('7');
insert into [Table] values ('8');
insert into [Table] values ('9');
insert into [Table] values ('10');
insert into [Table] values ('11');
insert into [Table] values ('12');